﻿/*
 * 分页处理相关类
 * 
 * 
 * 
 * 
 * 
 * 
 * Last Updated By Zeus2
 * 更新内容，采用连接池使用完即关闭，无需关闭连接。
 * Updated Time 2009-1-13
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace HZ.Data
{
    using HZ;
    using System.Data;

    #region MyRegion
    /// <summary>
    /// SQL语句分析类,可以分析无分组语句.
    /// 若有连接常用数据使用视图封装
    /// 并生成分页SQL语句
    /// *****已过期*****
    /// </summary>
    internal class SQLanalysis
    {
        #region Field AND Property
        private string sql;

        public string SqlString
        {
            get { return sql; }
            set { sql = value; analysis(); }
        }
        private string fields;
        ///// <summary>
        ///// 字段
        ///// </summary>
        //public string Fields
        //{
        //    get { return fields; }
        //    set { fields = value; }
        //}

        ///// <summary>
        ///// 获取字段数组
        ///// </summary>
        //public string[] FieldArray
        //{
        //    get
        //    {
        //        string[] result = fields.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
        //        for (int i = 0; i < result.Length; i++)
        //        {
        //            result[i] = result[i].Trim();
        //        }
        //        return result;
        //    }
        //}
        private string order;
        /// <summary>
        /// 排序
        /// </summary>
        //public string Order
        //{
        //    get { return order; }
        //    set { order = value; }
        //}
        private string table;
        ///// <summary>
        ///// 表名
        ///// </summary>
        //public string Table
        //{
        //    get { return table; }
        //    set { table = value; }
        //}
        private string condition;
        ///// <summary>
        ///// 查询条件
        ///// </summary>
        //public string Condition
        //{
        //    get { return condition; }
        //    set { condition = value; }
        //}
        /// <summary>
        /// 获取查询总数的SQL语句
        /// </summary>
        public string GetCountQueryString
        {
            get
            {
                return GetCountSQL();
            }
        }



        #endregion

        public SQLanalysis()
        {

        }

        // SQL语句分析类，排序字段不能为空。
        public SQLanalysis(string Sql)
        {
            sql = Sql;
            analysis();
        }

        /// <summary>
        /// 生成分页查询语句.该语句并不判断记录总数.请自行判断
        /// </summary>
        /// <param name="pagenum">需要查询的页面(从第一页开始计算)</param>
        /// <param name="pagination">每页的数目</param>
        /// <returns></returns>
        public string GetSQL(int pagenum, int pagination)
        {
            //string newsql = "SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS rownumber FROM XX ";

            // 展开Field???

            /*if (fields == "*")
            {
                using (SQLHelper helper = new SQLHelper())
                {
                    fields = "";
                    string sqlstr = string.Format("SELECT Column_Name FROM INFORMATION_SCHEMA.Columns WHERE Table_Name = '{0}'", table);

                    DataTable tb = helper.ExecuteTable(sqlstr);
                    for (int i = 0; i < tb.Rows.Count; i++)
                    {
                        if (i == 0)
                        {
                            fields += tb.Rows[i][0].ToString();
                        }
                        else
                        {
                            fields += "," + tb.Rows[i][0].ToString();
                        }
                    }

                }
            }*/

            if (string.IsNullOrEmpty(order))
            {
                if (fields.Trim() != "*")
                {

                    order = fields;
                }
                else
                {
                    throw new Exception("字段和排序不能都空");
                }
            }
            string newsql;
            if (!string.IsNullOrEmpty(condition))
            {
                if (pagenum == 1)
                {
                    newsql = string.Format("SELECT TOP {0} {2} FROM {1} WHERE {3} ORDER BY {4}", pagination, table, fields, condition, order);
                }
                else
                {
                    newsql = string.Format("SELECT {0} FROM (SELECT TOP {5} {0} ,ROW_NUMBER() OVER (ORDER BY {1}) AS rownumber FROM {2} WHERE {3}) AS A  WHERE A.rownumber BETWEEN {4} AND {5} ORDER BY {1}", fields, order, table, condition, (pagenum - 1) * pagination + 1, pagenum * pagination);
                }
            }
            else
            {
                if (pagenum == 1)
                {
                    newsql = string.Format("SELECT TOP {0} {2} FROM {1} ORDER BY {3}", pagination, table, fields, order);
                }
                else
                {
                    newsql = string.Format("SELECT {0} FROM (SELECT TOP {4} {0} ,ROW_NUMBER() OVER (ORDER BY {1}) AS rownumber FROM {2}) AS A  WHERE A.rownumber BETWEEN {3} AND {4} ORDER BY {1}", fields, order, table, (pagenum - 1) * pagination + 1, pagenum * pagination);
                }
            }
            return newsql;
        }



        #region 私有方法

        private string GetCountSQL()
        {
            string sql;
            if (string.IsNullOrEmpty(condition))
            {

                sql = string.Format("SELECT COUNT(*) FROM {0}", table);
            }
            else
            {
                sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}", table, condition);
            }
            return sql;
        }

        private void analysis()
        {
            sql = sql.ToLower();
            string[] split = sql.Split(new string[] { "select", "from", "where", "order by" }, StringSplitOptions.RemoveEmptyEntries);
            if (sql.Contains("group") && sql.Contains("by"))
            {
                throw new Exception("暂不支持group语句");
            }
            fields = split[0].Trim();
            table = split[1].Trim();
            if (split.Length == 3)
            {
                if (sql.Contains("where"))
                {
                    condition = split[2].Trim();
                }
                else
                {
                    order = split[2].Trim();
                }
            }

            if (split.Length > 3)
            {
                condition = split[2].Trim();
                order = split[3].Trim();
            }
        }

        #endregion

    }
    #endregion

    /// <summary>
    /// 分页处理类，用于获取指定页码的Sql语句。可供Winform /Webform控件调用。
    /// </summary>
    internal class PaginationYn
    {
        private string _sql;
        public static int PageSize; //默认值为10
        //缓存计算结果
        private static Dictionary<string, string> querycache = new Dictionary<string, string>();

        /// <summary>
        /// 分页分析类。语句格式必须为Select %s FROM %s [Group BY %s [Having %s]] [WHERE %S] ORDER BY %S
        /// 表名可INNER JOIN的联合表名也可直接2个表名
        /// </summary>
        /// <param name="querystring"></param>
        public PaginationYn(string querystring)
        {
            _sql = Regex.Replace(querystring, @"\s{2,}", " ");
        }

        static PaginationYn()
        {
            string spagesize = System.Configuration.ConfigurationManager.AppSettings["pagesize"];
            if (spagesize == null)
            {
                PageSize = 10;
            }
            else
            {
                if (!int.TryParse(spagesize, out PageSize))
                {
                    PageSize = 10;
                }
            }
        }

        /// <summary>
        /// 获取指定页码数据的查询语句
        /// </summary>
        /// <param name="page">要查询第几页</param>
        /// <param name="pagesize">每页数据数，不分页选择0.</param>
        /// <returns></returns>
        public string GetSpecialPageSql(int page, int pagesize, int totalCount)
        {
            if (pagesize == 0)
            {
                return _sql;
            }
            if (page >= 300 && totalCount < 20000) //数据量过大则不使用临时表
            {
                _sql = _sql + "/*300*/";
            }

            StringBuilder sqlcopy = new StringBuilder(32);
            if (querycache.ContainsKey(_sql)) // 使用构造缓存
            {
                sqlcopy = new StringBuilder(querycache[_sql]);
            }
            else
            {
                Regex regdistinct = new Regex(@"(SELECT\s+DISTINCT)|(GROUP\s+BY)", RegexOptions.IgnoreCase);

                if (!regdistinct.IsMatch(_sql))
                {
                    #region 正常语句

                    Regex sqlregex = new Regex(@"select(?<field>.+?)from(?<table>.+)(where(?<where>.+))?order\s+by(?<order>.+)", RegexOptions.IgnoreCase); // 必须有order by
                    if (page < 300 || true) //暂不使用
                    {
                        if (!sqlregex.IsMatch(_sql))
                        {
                            throw new Exception("语句不符合规范");
                        }
                        Match match = sqlregex.Match(_sql);

                        string field = match.Groups["field"].Value;
                        string table = match.Groups["table"].Value;
                        string where = match.Groups["where"].Value;
                        string order = match.Groups["order"].Value;

                        if (string.IsNullOrEmpty(where))
                        {
                            sqlcopy.AppendFormat("SELECT * FROM (SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS POS FROM {2}) AS _TB", field, order, table);
                        }
                        else
                        {
                            sqlcopy.AppendFormat("SELECT * FROM (SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS POS FROM {2} WHERE {3}) AS _TB", field, order, table, where);
                        }
                        //
                    }
                    else
                    {
                        //大分页使用使用临时表
                        if (!sqlregex.IsMatch(_sql))
                        {
                            throw new Exception("语句不符合规范");
                        }
                        Match match = sqlregex.Match(_sql);

                        string field = match.Groups["field"].Value;
                        string table = match.Groups["table"].Value;
                        string where = match.Groups["where"].Value;
                        string order = match.Groups["order"].Value;

                        if (string.IsNullOrEmpty(where))
                        {
                            sqlcopy.AppendFormat("SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS POS Into #Temptable FROM {2}", field, order, table);
                        }
                        else
                        {
                            sqlcopy.AppendFormat("SELECT {0},ROW_NUMBER() OVER(ORDER BY {1}) AS POS Into #Temptable FROM {2} WHERE {3}", field, order, table, where);
                        }

                        sqlcopy.Append(";SELECT * FROM  #Temptable AS _TB");

                    }
                    #endregion
                }
                else
                {
                    // 包含DISTINCT和Group BY语句处理
                    Regex sqlregex = new Regex(@"select\s+distinct(?<field>.+?)from(?<table>.+?)(where(?<where>.+))?order\s+by(?<order>.+)", RegexOptions.IgnoreCase);

                    if (sqlregex.IsMatch(_sql))
                    {
                        Match match = sqlregex.Match(_sql);
                        string field = match.Groups["field"].Value;
                        string table = match.Groups["table"].Value;
                        string where = match.Groups["where"].Value;
                        string order = match.Groups["order"].Value;
                        Regex prefix = new Regex(@"(\w+\.)");
                        order = prefix.Replace(order, "");
                        string sqlwithoutorder = Regex.Replace(_sql, @"(order\s+by.*)", "", RegexOptions.IgnoreCase);
                        if (string.IsNullOrEmpty(order))
                        {
                            sqlcopy.AppendFormat("SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY {0}) AS POS FROM ({1}) AS _tb) AS _TB", field, sqlwithoutorder);
                        }
                        else
                        {
                            sqlcopy.AppendFormat("SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY {0}) AS POS FROM ({1}) AS _tb) AS _TB", order, sqlwithoutorder);
                        }
                    }
                    else
                    {
                        Regex groupby = new Regex(@"select\s+(?<field>.+?)from\s+(?<table>.+)group\s+by\s+(?<group>.+)order\s+by\s+(?<order>.+)", RegexOptions.IgnoreCase);
                        Match m = groupby.Match(_sql);


                        string field = m.Groups["field"].Value;
                        string table = m.Groups["table"].Value;
                        string where = m.Groups["group"].Value;
                        string order = m.Groups["order"].Value;
                        Regex prefix = new Regex(@"(\w+\.)");
                        order = prefix.Replace(order, "");
                        string sqlwithoutorder = Regex.Replace(_sql, @"(order\s+by.*)", "", RegexOptions.IgnoreCase);
                        sqlcopy.AppendFormat("SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY {0}) AS POS FROM ({1}) AS _tb) AS _TB", order, sqlwithoutorder);
                    }


                }
                querycache[_sql] = sqlcopy.ToString();
            }
            sqlcopy.AppendFormat(" WHERE _TB.POS BETWEEN {0} AND {1}", (page - 1) * pagesize + 1, page * pagesize);
            return sqlcopy.ToString();
        }

        public string GetSpecialPageSql(int page)
        {
            return GetSpecialPageSql(page, PageSize, 0);
        }

        /// <summary>
        /// 获取结果数目
        /// </summary>
        /// <returns></returns>
        public string GetCountSql()
        {
            string r = string.Empty;

            Regex distinct = new Regex(@"select\s+distinct|group\s+by", RegexOptions.IgnoreCase);

            if (distinct.IsMatch(_sql))
            {
                string coutsql = Regex.Replace(_sql, @"(order\s+by.*)", "", RegexOptions.IgnoreCase);
                return string.Format("SELECT Count(*) FROM ({0}) As _TB", coutsql);
            }
            else
            {
                string coutsql = Regex.Replace(_sql, @"(order\s+by.*)", "", RegexOptions.IgnoreCase);
                Regex regex = new Regex(@"select\s+(?<field>.+?)from", RegexOptions.IgnoreCase);
                Match m = regex.Match(coutsql);
                string field = m.Groups["field"].Value;
                return coutsql.Replace(field, "Count(*) ");
            }
        }
    }


    /// <summary>
    /// 分页描述类，封装Pagination数据。
    /// 自动获取分页后的数据。
    /// 可跟表现层分页控件合用。 
    /// 
    /// 
    /// 云南特有的分页处理，以后继续合并！！！
    /// </summary>
    public class PageDescribeYn
    {
        #region Fields AND Property

        private bool _enableCache = true;
        /// <summary>
        /// 是否启用缓存来获取数据。
        /// </summary>
        public bool EnableCache
        {
            get { return _enableCache; }
            set { _enableCache = value; }
        }
        private int _pagesize;
        /// <summary>
        /// 每页数据数目
        /// </summary>
        public int PageSize
        {
            get { return _pagesize; }
            set { _pagesize = value; }
        }
        private int _totalcount;

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int TotalCount
        {
            get { return _totalcount; }

        }
        private int _currentpage;

        /// <summary>
        /// 设置或获取当前页码
        /// </summary>
        public int CurrentPage
        {
            get { return _currentpage; }
            set
            {
                _currentpage = value;
                if (_currentpage <= 0)
                {
                    _currentpage = 1;
                }
            }
        }

        /// <summary>
        /// 根据PageSize获取总页数
        /// </summary>
        public int MaxPage
        {
            get
            {
                return (int)((double)_totalcount / _pagesize + 0.99999);
            }
        }
        #endregion
        PaginationYn pagination;
        /// <summary>
        /// 获取当前页(CurrentPage)的数据
        /// </summary>
        /// <returns>当前页的结果</returns>
        public DataTable GetSpecailPage()
        {
            if (CurrentPage <= 0)
            {
                CurrentPage = 1;
            }
            // DataTable tb = null; // 过期
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(pagination.GetSpecialPageSql(CurrentPage, _pagesize, _totalcount));

        }
        /// <summary>
        /// 语句分页处理
        /// </summary>
        /// <param name="sql">待分页的查询语句</param>
        /// <param name="pagesize">每页数据数目</param>
        public PageDescribeYn(string sql, int pagesize)
        {
            pagination = new PaginationYn(sql);
            _pagesize = pagesize;
            _totalcount = (int)HZ.Data.DbHelperFactory.Create().ExecuteScalar(pagination.GetCountSql());

        }

        /// <summary>
        /// 是否最后页
        /// </summary>
        /// <param name="page">页码</param>
        /// <returns>若为最后页返回true，否则返回false</returns>
        public bool IsLastPage(int page)
        {
            return page == MaxPage;
        }
        /// <summary>
        /// 关闭连接，已不需要执行。
        /// </summary>
        [Obsolete("该方法已过期，无需执行。")]
        public void Close()
        {

            // helper.Close();
        }
    }

}